from generate_common import handleCustomApply
from generate_violate_item_num import genViolateItemNum
import os
import sys
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# 读取 .env 文件
import dotenv
from config import create_db_engine
# 创建数据库引擎
engine = create_db_engine()
dotenv.load_dotenv()
from sqlalchemy import text

def genViolateReplace(ruleDatum, ruleAct, hsp_abbr):
    """
    violate_replace，优先，串换计算规则
    实际收费是 B，应该收费是 A（itemscene）

    1. va 更新 accu_q，按照 关联键 作为分类条件，按照 p 升序、d0_rowid 升序排列，计算 accu_q
    2. va 更新 num1，按照 关联键 作为分类条件，按照 p 升序、d0_rowid 升序排列，row_number
    3. va 计算 vlt_q
        a. 如果 row_number = 1，那么就是第一条，如果 q = > value，那么 value 就是违规数量；否则 q 就是违规数量
        b. 如果 row_number > 1，那么就是后续条目，判断 value - 上一条的 accu_q，如果大于当前的 q，那么 q 就是违规数量；否则 value - 上一条的 accu_q 就是违规数量
    4. va 统一更新 vlt_p 为空
    5. va 更新 num2。这个 num2 是为了后续计算 vlt_c 准备的，是从 itemscene 中查出的单价。
        a. 当 num1=1 的时候，num2 就是 itemscene 对应的单价（即数量为 1 时候的金额）。在 scene 中查出单价 p2，num2=p2
        b. 当 num1>1 的时候，num2=0
    6. va 更新 vlt_c。违规金额就是差价，即 value 个加收项目应该替换为 1 个主项目，那么差价就是违规金额。如果差价 < 0，那么违规金额就是 0。
        a. 当 num1=1 的时候，vlt_c 就是当前 joinKeys 下的 min(p) * value - p2
        b. 当 num1>1 的时候，vlt_c=0
    7. va 更新 vlt_b。vlt_b = vlt_c * b / c。

    参数
    ● value：B 收费应该替换为 A 的数量比如说 5 个加收项目应该替换为 1 个主项目，那么就应该拿 5 个 B 项目替换为 1 个 A 项目，
        ○ 其中差价就是违规金额
        ○ value 与 accu_q 的最小值则视为违规数量。即能够替换为主项目的数量。
    ● in_one_：比较方法
        ○ day：在一天内比较，关联键是 setl_id, item_day
        ○ case：在一个病例下比较，关联键是 setl_id
    """
    
    # 获得value
    value = ruleAct['violate_replace']['value']
    
    # 获得 inOne
    inOne = ruleAct['violate_replace'].get('in_one_', 'case')
    
    # 根据 inOne 制作关联键
    if inOne == 'day':
        joinKeys = ['setl_id', 'item_day']
    else:
        joinKeys = ['setl_id']
    
    # 获得 itemscene_apply 
    itemscene = ruleAct['itemscene']
    # 制作为 SQL 的语句，直接查 stable_serv_price （sp）表
    itemscene_sql = " OR ".join([f"sp.item_code = '{item}'" for item in itemscene])
    
    sqlList = [
        # step1
        f"""
            -- violate replace，step1，va 更新 accu_q，按照 关联键 作为分类条件，按照 p 升序、d0_rowid 升序排列，计算 accu_q
            MERGE INTO vlt_tmp_a va
            USING (
                SELECT
                    d0_rowid,
                    SUM(q) OVER (
                        PARTITION BY {', '.join([f'va.{key}' for key in joinKeys])}
                        ORDER BY p ASC, d0_rowid ASC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS accu_q
                FROM vlt_tmp_a va
            ) sub
            ON (va.d0_rowid = sub.d0_rowid)
            WHEN MATCHED THEN
            UPDATE SET va.accu_q = sub.accu_q
        """,
        # step2
        f"""
            -- violate replace，step2，va 更新 num1，按照 关联键 作为分类条件，按照 p 升序、d0_rowid 升序排列，row_number
            MERGE INTO vlt_tmp_a va
            USING (
                SELECT
                    d0_rowid,
                    ROW_NUMBER() OVER (
                        PARTITION BY {', '.join([f'va.{key}' for key in joinKeys])}
                        ORDER BY p ASC, d0_rowid ASC
                    ) AS num1
                FROM vlt_tmp_a va
            ) sub
            ON (va.d0_rowid = sub.d0_rowid)
            WHEN MATCHED THEN
            UPDATE SET va.num1 = sub.num1
        """,
        # step3
        f"""
            -- violate replace，step3，va 计算 vlt_q
            UPDATE vlt_tmp_a va
            SET vlt_q = CASE
                WHEN va.num1 = 1 THEN LEAST(va.q, {value})
                ELSE LEAST(va.q, GREATEST({value} - (
                    SELECT sub_prev.accu_q
                    FROM (
                        SELECT
                            d0_rowid,
                            accu_q,
                            {', '.join([f'{key}' for key in joinKeys])}
                        FROM vlt_tmp_a
                    ) sub_prev
                    WHERE sub_prev.d0_rowid < va.d0_rowid
                      AND { ' AND '.join([f'sub_prev.{key} = va.{key}' for key in joinKeys]) }
                    ORDER BY d0_rowid DESC
                    FETCH FIRST 1 ROW ONLY
                ), 0))
            END
        """,
        # step4
        f"""
            -- violate replace，step4，va 统一更新 vlt_p 为空
            UPDATE vlt_tmp_a va
            SET vlt_p = NULL
        """,
        # step5
        f"""
            -- violate replace，step5，va 更新 num2。这个 num2 是为了后续计算 vlt_c 准备的，是从 itemscene 中查出的单价。
            UPDATE vlt_tmp_a va
            SET num2 = CASE
                WHEN va.num1 = 1 THEN (
                    SELECT sp.price
                    FROM stable_serv_price sp
                    WHERE sp.hsp_abbr = va.hsp_abbr
                      AND ( 1=0 OR {itemscene_sql} )
                    ORDER BY sp.price ASC
                    FETCH FIRST 1 ROW ONLY
                )
                ELSE 0
            END
        """,
        # step6
        f"""
            -- violate replace，step6，va 更新 vlt_c。违规金额就是差价，即 value 个加收项目应该替换为 1 个主项目，那么差价就是违规金额。如果差价 < 0，那么违规金额就是 0。
            UPDATE vlt_tmp_a va
            SET vlt_c = CASE
                WHEN va.num1 = 1 
                THEN GREATEST(
                    LEAST(
                        (
                            SELECT MIN(sub.p)
                            FROM vlt_tmp_a sub
                            WHERE { ' AND '.join([f'sub.{key} = va.{key}' for key in joinKeys]) }
                        ) * {value} - va.num2,
                        99999999
                    ),
                    0
                )
                ELSE 0
            END
        """,
        # step7
        f"""
            -- violate replace，step7，va 更新 vlt_b。vlt_b = vlt_c * b / c。
            UPDATE vlt_tmp_a va
            SET vlt_b = CASE
                WHEN va.c IS NULL OR va.c = 0 THEN 0
                ELSE ROUND(va.vlt_c * va.b / va.c, 2)
            END
        """,
    ]
    
    return sqlList

def genViolateA99(ruleDatum, ruleAct, hsp_abbr):
    """
    实际收费是 B，应该收费是 A，而且 A 的单价是 B 的 1.25 倍（1/0.8）。利用vb表计算vlt_c。

    1. va 更新 accu_q，按照 关联键 作为分类条件，按照 p 降序、d0_rowid 升序排列，计算 accu_q
    2. va 更新 num1，按照 关联键 作为分类条件，按照 accu_q 降序排列，row_number
    3. 插入到 vb 表，统计 va 中的 setl_id, item_day 下的 sum_q， max(p) as p
    4. vb 计算每个 setl_id, item_day 下的 vlt_c，vlt_c=sum_q - 0.8 * p
    5. va 计算 vlt_q，accu_q - 2 的部分就是就是 vlt_q，但是不能小于 0，也不能大于 q
    6. va 统一更新 vlt_p 为空
    7. va 更新 vlt_c，vb 中同一个 setl_id, item_day 下的 vlt_c 更新过去
    8. va 更新 vlt_b。vlt_b = vlt_c * b / c。

    参数
    ● value：B 收费应该替换为 A 的数量比如说 5 个加收项目应该替换为 1 个主项目，那么就应该拿 5 个 B 项目替换为 1 个 A 项目，其中差价就是违规金额
    ● in_one_：比较方法
        ○ day：在一天内比较，关联键是 setl_id, item_day
        ○ case：在一个病例下比较，关联键是 setl_id
    """
    
    # 获得value
    value = ruleAct['violate_replace']['value']
    
    # 获得 inOne
    inOne = ruleAct['violate_replace'].get('in_one_', 'case')
    
    # 根据 inOne 制作关联键
    if inOne == 'day':
        joinKeys = ['setl_id', 'item_day']
    else:
        joinKeys = ['setl_id']
    
    sqlList = [
        # step1
        f"""
            -- violate A99，step1，va 更新 accu_q，按照 关联键 作为分类条件，按照 p 降序、d0_rowid 升序排列，计算 accu_q
            UPDATE vlt_tmp_a va
            SET accu_q = sub.accu_q
            FROM (
                SELECT
                    d0_rowid,
                    SUM(q) OVER (
                        PARTITION BY {', '.join([f'va.{key}' for key in joinKeys])}
                        ORDER BY p DESC, d0_rowid ASC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS accu_q
                FROM vlt_tmp_a va
            ) AS sub
            WHERE va.d0_rowid = sub.d0_rowid
        """,
        # step2
        f"""
            -- violate A99，step2，va 更新 num1，按照 关联键 作为分类条件，按照 accu_q 降序排列，row_number
            UPDATE vlt_tmp_a va
            SET num1 = sub.num1
            FROM (
                SELECT
                    d0_rowid,
                    ROW_NUMBER() OVER (
                        PARTITION BY {', '.join([f'va.{key}' for key in joinKeys])}
                        ORDER BY accu_q DESC
                    ) AS num1
                FROM vlt_tmp_a va
            ) AS sub
            WHERE va.d0_rowid = sub.d0_rowid
        """,
        # step3
        f"""TRUNCATE TABLE vlt_tmp_b""",
        f"""
            -- violate A99，step3，插入到 vb 表，统计 va 中的 setl_id, item_day 下的 sum_q， max(p) as p
            INSERT INTO vlt_tmp_b (setl_id, item_day, sum_q, p)
            SELECT 
                setl_id,
                item_day,
                SUM(q) AS sum_q,
                MAX(p) AS p
            FROM vlt_tmp_a va
            GROUP BY setl_id, item_day
        """,
        # step4
        f"""
            -- violate A99，step4，vb 计算每个 setl_id, item_day 下的 vlt_c，vlt_c=sum_q - 0.8 * p
            UPDATE vlt_tmp_b vb
            SET vlt_c = vb.sum_q - 0.8 * vb.p
        """,
        # step5
        f"""
            -- violate A99，step5，va 计算 vlt_q，accu_q - 2 的部分就是就是 vlt_q，但是不能小于 0，也不能大于 q
            UPDATE vlt_tmp_a va
            SET vlt_q = CASE
                WHEN va.accu_q - {value} > va.q THEN va.q
                WHEN va.accu_q - {value} <= 0 THEN 0
                ELSE va.accu_q - {value}
            END
        """,
        # step6
        f"""
            -- violate A99，step6，va 统一更新 vlt_p 为空
            UPDATE vlt_tmp_a va
            SET vlt_p = NULL
        """,
        # step7
        f"""
            -- violate A99，step7，va 更新 vlt_c，vb 中同一个 setl_id, item_day 下的 vlt_c 更新过去
            UPDATE vlt_tmp_a va
            SET vlt_c = vb.vlt_c
            FROM vlt_tmp_b vb
            WHERE va.setl_id = vb.setl_id AND va.item_day = vb.item_day
        """,
        # step8
        f"""
            -- violate A99，step8，va 更新 vlt_b。vlt_b = vlt_c * b / c。
            UPDATE vlt_tmp_a va
            SET vlt_b = CASE
                WHEN va.c IS NULL OR va.c = 0 THEN 0
                ELSE ROUND(va.vlt_c * va.b / va.c, 2)
            END
        """,
    ]
    
    return sqlList

def genViolatePricePart(ruleDatum, ruleAct, hsp_abbr):
    """
    1. （仅当 by=diff）从 itemscene 查出对应 j_year, j_isin 的单价，插入到 vb 当中，占用 vb 的 p
    2. 计算 va 的 vlt_p
        a. 当 by=diff：vlt_p = 对应的 ( plus_before_times + GREATEST( va.p - vb.p , 0 ) ) * times + plus_after_times
        b. else：vlt_p = ( plus_before_times + GREATEST( va.p , 0 ) ) * times + plus_after_times

    参数
    ● by：聚类方法，有 default, asc, min, diff
        ○ default 或 min 或 asc：出现的都认为是违规，由最小单价给出。默认的处理方式就可以
        ○ diff：差价，与 itemscene 中的项目单价中的差价。
    ● plus_before_times：默认 0
    ● times：校正系数，默认 1
    ● plus_after_times：默认 0
    ● at_most：最多不超过
    """
    
    # 获得 by, plus_before_times, times, plus_after_times, at_most
    by = ruleAct['violate_price'].get('by', 'default')
    plus_before_times = ruleAct['violate_price'].get('plus_before_times', 0)
    times = ruleAct['violate_price'].get('times', 1)
    plus_after_times = ruleAct['violate_price'].get('plus_after_times', 0)
    at_most = ruleAct['violate_price'].get('at_most', None)
    at_most_sql = 'NULL' if at_most is None else str(at_most)
    
    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b",
    ]
    
    # step1
    if by == 'diff':
        # 获得 itemscene（仅当 by=diff 时需要），并且制作 sql 语句
        itemscene = ruleAct['itemscene']
        itemscene_sql = " OR ".join([f"sp.item_code = '{item}'" for item in itemscene])
        if len(itemscene) == 0:
            raise ValueError("当 by=diff 时，itemscene 不能为空")
        
        sqlList.append(
            f"""
                -- violate price part，step1，（仅当 by=diff）从 itemscene 查出对应 j_year, j_isin 的单价，插入到 vb 当中，占用 vb 的 p
                INSERT INTO vlt_tmp_b (p)
                SELECT max(sp.price) AS p
                FROM stable_serv_price sp
                WHERE sp.hsp_abbr = '{hsp_abbr}'
                    AND ( 1=0 OR {itemscene_sql} )
            """
        )
    
    # step2
    if by == 'diff':
        sqlList.append(
            f"""
                -- violate price part，step2，计算 va 的 vlt_p，当 by=diff：vlt_p = 对应的 ( plus_before_times + GREATEST( va.p - vb.p , 0 ) ) * times + plus_after_times
                MERGE INTO vlt_tmp_a va
                USING vlt_tmp_b vb
                ON (va.d0_rowid = vb.d0_rowid)
                WHEN MATCHED THEN
                UPDATE SET vlt_p = LEAST(
                    CASE
                        WHEN vb.p IS NOT NULL THEN ({plus_before_times} + GREATEST(va.p - vb.p, 0)) * {times} + {plus_after_times}
                        ELSE NULL
                    END,
                    {at_most_sql}  -- 如果 at_most 有值，则限制最大值
                )
            """
        )
    else:
        if at_most is not None:
            sqlList.append(
                f"""
                    -- violate price part，step2，计算 va 的 vlt_p，当 by!=diff：vlt_p = ( plus_before_times + GREATEST( va.p , 0 ) ) * times + plus_after_times，并且限制最大值
                    UPDATE vlt_tmp_a va
                    SET vlt_p = LEAST(
                        ({plus_before_times} + GREATEST(va.p, 0)) * {times} + {plus_after_times},
                        {at_most_sql}
                    )
                """
            )
        else:
            sqlList.append(
                f"""
                    -- violate price part，step2，计算 va 的 vlt_p，当 by!=diff：vlt_p = ( plus_before_times + GREATEST( va.p , 0 ) ) * times + plus_after_times
                    UPDATE vlt_tmp_a va
                    SET vlt_p = ({plus_before_times} + GREATEST(va.p, 0)) * {times} + {plus_after_times}
                """
            )
    
    return sqlList

def genViolates(ruleDatum, ruleAct, hsp_abbr):
    # 获取 ruleId, ruleActId
    ruleId = ruleDatum['ruleId']
    ruleActId = ruleAct['ruleActId']

    # ============ 优先处理 ============
    # 判断ruleAct是否有 violate_replace，如果有，则按照这种情形处理，最优先
    if 'violate_replace' in ruleAct:
        return genViolateReplace(ruleDatum, ruleAct, hsp_abbr)
    # 判断ruleAct是否是 A99，如果有，则按照这种情形处理，次优先
    elif 'violate_A99' in ruleAct:
        return genViolateA99(ruleDatum, ruleAct, hsp_abbr)
    
    # ============ 按顺序处理 ============
    sqlList = []
    # 计算 vlt_p
    if 'violate_price' in ruleAct:
        # 针对 violate_price 的处理
        sqlList += genViolatePricePart(ruleDatum, ruleAct, hsp_abbr)
    else:
        # 不需要特别处理的情况
        sqlList.append(
            f"""
                -- violate price，默认处理，使的没有被更新的 vlt_p 直接等于 p
                UPDATE vlt_tmp_a va SET va.vlt_p = va.p WHERE va.vlt_p IS NULL
            """
        )
    
    # 针对 violate_num 的处理
    # 实际上不需要对数量做什么处理，暂时留空
    sqlList += [
        f"""
            -- violate num，默认处理，使的没有被更新的 vlt_q 直接等于 q
            UPDATE vlt_tmp_a va SET va.vlt_q = va.q WHERE va.vlt_q IS NULL
        """
    ]
    
    # 计算 vlt_c, vlt_b
    sqlList += [
        # vlt_c
        f"""
            -- 计算 vlt_c
            UPDATE vlt_tmp_a va SET va.vlt_c = va.vlt_p * va.vlt_q WHERE va.vlt_c IS NULL
        """,
        # vlt_b
        f"""
            -- 计算 vlt_b
            UPDATE vlt_tmp_a va
            SET va.vlt_b = CASE
                WHEN va.c IS NULL OR va.c = 0 THEN 0
                ELSE va.vlt_c * va.b / va.c
            END
            WHERE va.vlt_b IS NULL
        """,
    ]
    
    
    return sqlList